import pymysql
from . import items

cont = pymysql.connect(host='127.0.0.1', user='root', password='h303567469', db='fd', charset='utf8',
                       cursorclass=pymysql.cursors.DictCursor)
cursor = cont.cursor()


def label_is_exist(lab):
    sql = """select count(*) from wylh_label where wylh_label.lab= "%s";""" % lab

    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        return True if results[0]['count(*)'] >= 1 else False
    except:
        print("error: select label count(*)")


def update_to_old_label(lab_name, new_date_s):
    sql = """update wylh_tingpan set wylh_tingpan.full_time='%s' 
    where wylh_tingpan.name = '%s' and wylh_tingpan.full_time > '%s'""" % (new_date_s, lab_name, new_date_s)
    try:
        cursor.execute(sql)
        cont.commit()
    except:
        print("error: select label count(*)")


def insert_label(label, date_s):
    sql = "insert into `wylh_label` (`lab`,`rec_time`) values('%s','%s');" % (label, date_s)
    try:
        cursor.execute(sql)
        cont.commit()
    except:
        print("error: insert_label ")
        cont.rollback()


def insert_tp_item_many(tp_items):
    one_item = tp_items[0]
    keys = one_item.keys()
    params = list()
    fields = ','.join(keys)
    values_space_holder = ','.join(['%s'] * len(keys))

    for tp_item in tp_items:
        print(tp_item)
        params.append(tuple(tp_item.values()))

    sql = "insert into `wylh_tingpan` (%s) values(%s)" % (fields, values_space_holder)  # 这里似乎和insert不同
    print("sql:%s ; %s", sql, values_space_holder)
    print(params)
    try:
        cursor.executemany(sql, args=params)
        cont.commit()
    except Exception as e:
        print(e)
        print("error: insert_tp_item_many")
        cont.rollback()


def insert_tp_item(tp_item):
    keys = tp_item.keys()
    fields = ','.join(keys)
    values_place_holder = ','.join(['%s'] * len(keys))

    sql = "insert into `wylh_tingpan` (%s) values(%s)" % (fields, values_place_holder)
    # print(type(tp_item.values()))
    # 进行类型转化

    try:
        cursor.execute(sql, args=tuple(tp_item.values()))
        cont.commit()
    except Exception as e:
        print(e)
        print("error: insert tp_item ")
        cont.rollback()
